﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.OracleClient;

namespace Sinacor.Infra.Tools.ServiceRegistration.Data
{
    public class Module : ResourceAccess
    {
        #region Methods

        public DataSet SelectAll()
        {
            StringBuilder sql = new StringBuilder();
            DataSet ds = new DataSet();

            sql.AppendLine("SELECT ");
            sql.AppendLine("      MOD.COD_MOD, ");
            sql.AppendLine("      MOD.NOME_MOD, ");
            sql.AppendLine("      MOD.SIGL_MOD, ");
            sql.AppendLine("      SUB.COD_SUB_MOD, ");
            sql.AppendLine("      SUB.NOME_SUB_MOD, ");
            sql.AppendLine("      SUB.NUM_PRTA_SERV, ");
            sql.AppendLine("      SVC.COD_SERV, ");
            sql.AppendLine("      SVC.NOME_SERV, ");
            sql.AppendLine("      SVC.DESC_SERV DESC_SERV, ");
            sql.AppendLine("      MTD.COD_MET_SERV, ");
            sql.AppendLine("      MTD.NOME_MET_SERV, ");
            sql.AppendLine("      MTD.DESC_MET_SERV DESC_MET_SERV, ");
            sql.AppendLine("      MTD.NOME_TABL_UTI_MET, ");
            sql.AppendLine("      MTD.IND_COLE IND_COLE_METODO, ");
            sql.AppendLine("      TPM.COD_TIPO_PARM_RETO_MET COD_TIPO_METODO, ");
            sql.AppendLine("      TPM.COD_TIPO_PARM_RETO_MET_PAI COD_TIPO_METODO_PAI, ");
            sql.AppendLine("      TPM.NOME_NAMS NOME_NAMS_METODO, ");
            sql.AppendLine("      TPM.NOME_TIPO_PARM_RETO_MET NOME_TIPO_METODO, ");
            sql.AppendLine("      PRT.COD_PARM, ");
            sql.AppendLine("      PRT.NOME_PARM_MET, ");
            sql.AppendLine("      PRT.DESC_PARM_MET DESC_PARM_MET, ");
            sql.AppendLine("      PRT.IND_COLE IND_COLE_PARAMETRO, ");
            sql.AppendLine("      TPP.COD_TIPO_PARM_RETO_MET COD_TIPO_PARAMETRO, ");
            sql.AppendLine("      TPP.COD_TIPO_PARM_RETO_MET_PAI COD_TIPO_PARAMETRO_PAI, ");
            sql.AppendLine("      TPP.NOME_NAMS NOME_NAMS_PARAMETRO, ");
            sql.AppendLine("      TPP.NOME_TIPO_PARM_RETO_MET NOME_TIPO_PARAMETRO");
            sql.AppendLine("FROM   ");
            sql.AppendLine("      TFWDMOD MOD LEFT JOIN ");
            sql.AppendLine("      TFWDSUB_MOD SUB ON ");
            sql.AppendLine("      MOD.COD_MOD = SUB.COD_MOD LEFT JOIN ");
            sql.AppendLine("      TFWDSERV SVC ON ");
            sql.AppendLine("      SUB.COD_SUB_MOD = SVC.COD_SUB_MOD LEFT JOIN ");
            sql.AppendLine("      TFWDMET_SERV MTD ON ");
            sql.AppendLine("      SVC.COD_SERV = MTD.COD_SERV LEFT JOIN ");
            sql.AppendLine("      TFWDTIPO_PARM_RETO_MET TPM ON ");
            sql.AppendLine("      MTD.COD_TIPO_PARM_RETO_MET = TPM.COD_TIPO_PARM_RETO_MET LEFT JOIN ");
            sql.AppendLine("      TFWDPARM_MET PRT ON ");
            sql.AppendLine("      MTD.COD_MET_SERV = PRT.COD_MET_SERV LEFT JOIN ");
            sql.AppendLine("      TFWDTIPO_PARM_RETO_MET TPP ON ");
            sql.AppendLine("      PRT.COD_TIPO_PARM_RETO_MET = TPP.COD_TIPO_PARM_RETO_MET ");
            sql.AppendLine("ORDER BY  ");
            sql.AppendLine("      MOD.NOME_MOD, ");
            sql.AppendLine("      SUB.NOME_SUB_MOD, ");
            sql.AppendLine("      SVC.NOME_SERV, ");
            sql.AppendLine("      MTD.NOME_MET_SERV, ");
            sql.AppendLine("      PRT.NOME_PARM_MET ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                using (OracleDataAdapter adapter = new OracleDataAdapter((OracleCommand)command))
                {
                    adapter.Fill(ds);
                }

                command.Connection.Close();
            }

            return ds;
        }

        public List<Entity.Module> SelectAll(Entity.Module entity)
        {
            List<Entity.Module> response = new List<Entity.Module>();

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("      MOD.COD_MOD, ");
            sql.AppendLine("      MOD.NOME_MOD, ");
            sql.AppendLine("      MOD.SIGL_MOD, ");
            sql.AppendLine("      SUB.COD_SUB_MOD, ");
            sql.AppendLine("      SUB.NOME_SUB_MOD, ");
            sql.AppendLine("      SUB.NUM_PRTA_SERV, ");
            sql.AppendLine("      SVC.COD_SERV, ");
            sql.AppendLine("      SVC.NOME_SERV, ");
            sql.AppendLine("      SVC.DESC_SERV DESC_SERV, ");
            sql.AppendLine("      MTD.COD_MET_SERV, ");
            sql.AppendLine("      MTD.NOME_MET_SERV, ");
            sql.AppendLine("      MTD.DESC_MET_SERV DESC_MET_SERV, ");
            sql.AppendLine("      MTD.NOME_TABL_UTI_MET, ");
            sql.AppendLine("      MTD.IND_COLE IND_COLE_METODO, ");
            sql.AppendLine("      TPM.COD_TIPO_PARM_RETO_MET COD_TIPO_METODO, ");
            sql.AppendLine("      TPM.COD_TIPO_PARM_RETO_MET_PAI COD_TIPO_METODO_PAI, ");
            sql.AppendLine("      TPM.NOME_NAMS NOME_NAMS_METODO, ");
            sql.AppendLine("      TPM.NOME_TIPO_PARM_RETO_MET NOME_TIPO_METODO, ");
            sql.AppendLine("      PRT.COD_PARM, ");
            sql.AppendLine("      PRT.NOME_PARM_MET, ");
            sql.AppendLine("      PRT.DESC_PARM_MET DESC_PARM_MET, ");
            sql.AppendLine("      PRT.IND_COLE IND_COLE_PARAMETRO, ");
            sql.AppendLine("      TPP.COD_TIPO_PARM_RETO_MET COD_TIPO_PARAMETRO, ");
            sql.AppendLine("      TPP.COD_TIPO_PARM_RETO_MET_PAI COD_TIPO_PARAMETRO_PAI, ");
            sql.AppendLine("      TPP.NOME_NAMS NOME_NAMS_PARAMETRO, ");
            sql.AppendLine("      TPP.NOME_TIPO_PARM_RETO_MET NOME_TIPO_PARAMETRO, ");
            sql.AppendLine("      MOD.DATA_ALTE DATA_ALTE_MOD, ");
            sql.AppendLine("      SUB.DATA_ALTE DATA_ALTE_SUB, ");
            sql.AppendLine("      SVC.DATA_ALTE DATA_ALTE_SVC, ");
            sql.AppendLine("      MTD.DATA_ALTE DATA_ALTE_MTD, ");
            sql.AppendLine("      PRT.DATA_ALTE DATA_ALTE_PRT, ");
            sql.AppendLine("      TPP.DATA_ALTE DATA_ALTE_TPP, ");
            sql.AppendLine("      TPM.DATA_ALTE DATA_ALTE_TPM, ");
            sql.AppendLine("      (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM   ");
            sql.AppendLine("      TFWDMOD MOD LEFT JOIN ");
            sql.AppendLine("      TFWDSUB_MOD SUB ON ");
            sql.AppendLine("      MOD.COD_MOD = SUB.COD_MOD LEFT JOIN ");
            sql.AppendLine("      TFWDSERV SVC ON ");
            sql.AppendLine("      SUB.COD_SUB_MOD = SVC.COD_SUB_MOD LEFT JOIN ");
            sql.AppendLine("      TFWDMET_SERV MTD ON ");
            sql.AppendLine("      SVC.COD_SERV = MTD.COD_SERV LEFT JOIN ");
            sql.AppendLine("      TFWDTIPO_PARM_RETO_MET TPM ON ");
            sql.AppendLine("      MTD.COD_TIPO_PARM_RETO_MET = TPM.COD_TIPO_PARM_RETO_MET LEFT JOIN ");
            sql.AppendLine("      TFWDPARM_MET PRT ON ");
            sql.AppendLine("      MTD.COD_MET_SERV = PRT.COD_MET_SERV LEFT JOIN ");
            sql.AppendLine("      TFWDTIPO_PARM_RETO_MET TPP ON ");
            sql.AppendLine("      PRT.COD_TIPO_PARM_RETO_MET = TPP.COD_TIPO_PARM_RETO_MET ");
            sql.AppendLine("WHERE  ");
            //sql.AppendLine("      (MOD.COD_MOD = :COD_MOD OR :COD_MOD IS NULL) AND ");
            sql.AppendLine("      (UPPER(MOD.NOME_MOD) LIKE UPPER(:NOME_MOD) OR :NOME_MOD IS NULL) AND ");
            sql.AppendLine("      (UPPER(MOD.SIGL_MOD) LIKE UPPER(:SIGL_MOD) OR :SIGL_MOD IS NULL) AND ");            
            //sql.AppendLine("      (SUB.COD_SUB_MOD = :COD_SUB_MOD OR :COD_SUB_MOD IS NULL) AND ");
            sql.AppendLine("      (UPPER(SUB.NOME_SUB_MOD) LIKE UPPER(:NOME_SUB_MOD) OR :NOME_SUB_MOD IS NULL) AND ");
            sql.AppendLine("      (SUB.NUM_PRTA_SERV = :NUM_PRTA_SERV OR :NUM_PRTA_SERV IS NULL) AND ");
            //sql.AppendLine("      (SVC.COD_SERV = :COD_SERV OR :COD_SERV IS NULL) AND ");
            sql.AppendLine("      (UPPER(SVC.NOME_SERV) LIKE UPPER(:NOME_SERV) OR :NOME_SERV IS NULL) AND ");
            sql.AppendLine("      (UPPER(SVC.DESC_SERV) LIKE UPPER(:DESC_SERV) OR :DESC_SERV IS NULL) AND ");
            //sql.AppendLine("      (MTD.COD_MET_SERV = :COD_MET_SERV OR :COD_MET_SERV IS NULL) AND ");
            sql.AppendLine("      (UPPER(MTD.NOME_MET_SERV) LIKE UPPER(:NOME_MET_SERV) OR :NOME_MET_SERV IS NULL) AND ");
            sql.AppendLine("      (UPPER(MTD.DESC_MET_SERV) LIKE UPPER(:DESC_MET_SERV) OR :DESC_MET_SERV IS NULL) AND ");
            sql.AppendLine("      (UPPER(MTD.NOME_TABL_UTI_MET) LIKE UPPER(:NOME_TABL_UTI_MET) OR :NOME_TABL_UTI_MET IS NULL) AND ");
            //sql.AppendLine("      (MTD.IND_COLE = :IND_COLE_METODO OR :IND_COLE_METODO IS NULL) AND ");
            //sql.AppendLine("      (TPM.COD_TIPO_PARM_RETO_MET = :COD_TIPO_METODO OR :COD_TIPO_METODO IS NULL) AND ");
            sql.AppendLine("      (UPPER(TPM.NOME_NAMS) LIKE UPPER(:NOME_NAMS_METODO) OR :NOME_NAMS_METODO IS NULL) AND ");
            sql.AppendLine("      (UPPER(TPM.NOME_TIPO_PARM_RETO_MET) LIKE UPPER(:NOME_TIPO_METODO) OR :NOME_TIPO_METODO IS NULL) AND ");
            //sql.AppendLine("      (PRT.COD_PARM = :COD_PARM OR :COD_PARM IS NULL) AND ");
            sql.AppendLine("      (UPPER(PRT.NOME_PARM_MET) LIKE UPPER(:NOME_PARM_MET) OR :NOME_PARM_MET IS NULL) AND ");
            sql.AppendLine("      (UPPER(PRT.DESC_PARM_MET) LIKE UPPER(:DESC_PARM_MET) OR :DESC_PARM_MET IS NULL) AND ");
            //sql.AppendLine("      (PRT.IND_COLE = :IND_COLE_PARAMETRO OR :IND_COLE_PARAMETRO IS NULL) AND ");
            //sql.AppendLine("      (TPP.COD_TIPO_PARM_RETO_MET = :COD_TIPO_PARAMETRO OR :COD_TIPO_PARAMETRO IS NULL) AND ");
            sql.AppendLine("      (UPPER(TPP.NOME_NAMS) LIKE UPPER(:NOME_NAMS_PARAMETRO) OR :NOME_NAMS_PARAMETRO IS NULL) AND ");
            sql.AppendLine("      (UPPER(TPP.NOME_TIPO_PARM_RETO_MET) LIKE UPPER(:NOME_TIPO_PARAMETRO) OR :NOME_TIPO_PARAMETRO IS NULL) ");

            if (entity.IsItemDivergent)
            {
                sql.AppendLine(" AND ( ");
                sql.AppendLine(" (MOD.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (SUB.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (SVC.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (MTD.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (PRT.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (TPP.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) OR ");
                sql.AppendLine(" (TPM.DATA_ALTE <> (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ) ");
            }
            else
            {
                sql.AppendLine(" AND (MOD.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR MOD.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (SUB.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR SUB.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (SVC.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR SVC.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (MTD.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR MTD.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (PRT.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR PRT.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (TPP.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR TPP.DATA_ALTE IS NULL) ");
                sql.AppendLine(" AND (TPM.DATA_ALTE = (SELECT MAX(DATA_ALTE) FROM TFWDMOD) OR TPM.DATA_ALTE IS NULL) ");
            }

            sql.AppendLine("ORDER BY  ");
            sql.AppendLine("      MOD.NOME_MOD, ");
            sql.AppendLine("      SUB.NOME_SUB_MOD, ");
            sql.AppendLine("      SVC.NOME_SERV, ");
            sql.AppendLine("      MTD.NOME_MET_SERV, ");
            sql.AppendLine("      PRT.NOME_PARM_MET ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                //this.AddInParameter(command, "COD_MOD", DbType.String, entity.ModuleId);
                this.AddInParameter(command, "NOME_MOD", DbType.String, AddCharacterLike(entity.ModuleName));
                this.AddInParameter(command, "SIGL_MOD", DbType.String, AddCharacterLike(entity.ModuleInitials));
                //this.AddInParameter(command, "COD_SUB_MOD", DbType.String, entity.SubModules[0].SubModuleId);
                this.AddInParameter(command, "NOME_SUB_MOD", DbType.String, AddCharacterLike(entity.SubModules[0].SubModuleName));
                this.AddInParameter(command, "NUM_PRTA_SERV", DbType.Int32, entity.SubModules[0].Port);
                //this.AddInParameter(command, "COD_SERV", DbType.Int32, entity.SubModules[0].Services[0].ServiceId);
                this.AddInParameter(command, "NOME_SERV", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].ServiceName));
                this.AddInParameter(command, "DESC_SERV", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Description));
                //this.AddInParameter(command, "COD_MET_SERV", DbType.Int32, entity.SubModules[0].Services[0].Methods[0].MethodId);
                this.AddInParameter(command, "NOME_MET_SERV", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].MethodName));
                this.AddInParameter(command, "DESC_MET_SERV", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].Description));
                this.AddInParameter(command, "NOME_TABL_UTI_MET", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].RelatedTables));
                //this.AddInParameter(command, "IND_COLE_METODO", DbType.String, entity.SubModules[0].Services[0].Methods[0].IsReturnCollection);
                //this.AddInParameter(command, "COD_TIPO_METODO", DbType.Int32, entity.SubModules[0].Services[0].Methods[0].ReturnType.TypeId);
                this.AddInParameter(command, "NOME_NAMS_METODO", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].ReturnType.Namespace));
                this.AddInParameter(command, "NOME_TIPO_METODO", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].ReturnType.TypeName));
                //this.AddInParameter(command, "COD_PARM", DbType.Int32, entity.SubModules[0].ModuleName);
                this.AddInParameter(command, "NOME_PARM_MET", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].Parameters[0].ParameterName));
                this.AddInParameter(command, "DESC_PARM_MET", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].Parameters[0].Description));
                //this.AddInParameter(command, "IND_COLE_PARAMETRO", DbType.String, entity.SubModules[0].ModuleName);
                //this.AddInParameter(command, "COD_TIPO_PARAMETRO", DbType.Int32, entity.SubModules[0].ModuleName);
                this.AddInParameter(command, "NOME_NAMS_PARAMETRO", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].Parameters[0].Type.Namespace));
                this.AddInParameter(command, "NOME_TIPO_PARAMETRO", DbType.String, AddCharacterLike(entity.SubModules[0].Services[0].Methods[0].Parameters[0].Type.TypeName));

                using (IDataReader reader = command.ExecuteReader())
                {
                    Entity.Module module = null;
                    Entity.SubModule submodule = null;
                    Entity.Service service = null;
                    Entity.Method method = null;
                    Entity.Parameter parameter = null;

                    while (reader.Read())
                    {
                        module = response.Find(match => match.ModuleId == reader.GetValue<int>("COD_MOD") );

                        if (module == null)
                        {
                            module = new Entity.Module() {
                                ModuleId = reader.GetValue<int>("COD_MOD"),
                                ModuleName = reader.GetValue<string>("NOME_MOD"),
                                ModuleInitials = reader.GetValue<string>("SIGL_MOD"),
                                DateChange = reader.GetValue<DateTime>("DATA_ALTE_MOD"),
                                IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE_MOD").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX"))
                            };
                        }

                        submodule = module.SubModules.Find(match => match.SubModuleId == reader.GetValue<int>("COD_SUB_MOD"));

                        if (submodule == null && reader.GetValue<int>("COD_SUB_MOD") != 0)
                        {
                            submodule = new Entity.SubModule
                            {
                                SubModuleId = reader.GetValue<int>("COD_SUB_MOD"),
                                SubModuleName = reader.GetValue<string>("NOME_SUB_MOD"),
                                DateChange = reader.GetValue<DateTime>("DATA_ALTE_SUB"),
                                IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE_SUB").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                                Module = module
                            };

                            module.SubModules.Add(submodule);
                        }

                        if (submodule != null)
                        {
                            service = submodule.Services.Find(match => match.ServiceId == reader.GetValue<int>("COD_SERV"));

                            if (service == null && reader.GetValue<int>("COD_SERV") != 0)
                            {
                                service = new Entity.Service
                                {
                                    ServiceId = reader.GetValue<int>("COD_SERV"),
                                    ServiceName = reader.GetValue<string>("NOME_SERV"),
                                    Description = reader.GetValue<string>("DESC_SERV"),
                                    DateChange = reader.GetValue<DateTime>("DATA_ALTE_SVC"),
                                    IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE_SVC").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                                    SubModule = submodule
                                };

                                submodule.Services.Add(service);
                            }
                        }

                        if (service != null)
                        {
                            method = service.Methods.Find(match => match.MethodId == reader.GetValue<int>("COD_MET_SERV"));

                            if (method == null && reader.GetValue<int>("COD_MET_SERV") != 0)
                            {
                                method = new Entity.Method
                                {
                                    MethodId = reader.GetValue<int>("COD_MET_SERV"),
                                    MethodName = reader.GetValue<string>("NOME_MET_SERV"),
                                    Description = reader.GetValue<string>("DESC_MET_SERV"),
                                    RelatedTables = reader.GetValue<string>("NOME_TABL_UTI_MET"),
                                    IsReturnCollection = reader.GetValue<string>("IND_COLE_METODO") == "S",
                                    ReturnType = new Entity.Type()
                                    {
                                        TypeId = reader.GetValue<int>("COD_TIPO_METODO"),
                                        ParentTypeId = reader.GetValue<int>("COD_TIPO_METODO_PAI"),
                                        Namespace = reader.GetValue<string>("NOME_NAMS_METODO"),
                                        TypeName = reader.GetValue<string>("NOME_TIPO_METODO"),
                                        //Attributes = GetAttributes(reader.GetValue<int>("COD_TIPO_METODO"))
                                    },
                                    DateChange = reader.GetValue<DateTime>("DATA_ALTE_MTD"),
                                    IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE_MTD").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                                    Service = service
                                };

                                service.Methods.Add(method);
                            }
                        }


                        if (service != null && method != null)
                        {
                            parameter = method.Parameters.Find(match => match.ParameterId == reader.GetValue<int>("COD_PARM"));

                            if (parameter == null && reader.GetValue<int>("COD_PARM") != 0)
                            {
                                parameter = new Entity.Parameter
                                {
                                    ParameterId = reader.GetValue<int>("COD_PARM"),
                                    ParameterName = reader.GetValue<string>("NOME_PARM_MET"),
                                    Description = reader.GetValue<string>("DESC_PARM_MET"),
                                    IsCollection = reader.GetValue<string>("IND_COLE_PARAMETRO") == "S",
                                    Type = new Entity.Type()
                                    {
                                        TypeId = reader.GetValue<int>("COD_TIPO_PARAMETRO"),
                                        ParentTypeId = reader.GetValue<int>("COD_TIPO_PARAMETRO_PAI"),
                                        Namespace = reader.GetValue<string>("NOME_NAMS_PARAMETRO"),
                                        TypeName = reader.GetValue<string>("NOME_TIPO_PARAMETRO"),
                                        //Attributes = GetAttributes(reader.GetValue<int>("COD_TIPO_PARAMETRO"))
                                    },
                                    DateChange = reader.GetValue<DateTime>("DATA_ALTE_PRT"),
                                    IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE_PRT").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX")),
                                    Method = method
                                };

                                method.Parameters.Add(parameter);
                            }
                        }

                        if (!response.Contains(module))
                            response.Add(module);
                    }
                }

                command.Connection.Close();
            }

            return response;
        }
                
        public List<Entity.Module> SelectModule(Entity.Module entity)
        {
            List<Entity.Module> response = null;

            StringBuilder sql = new StringBuilder();

            sql.AppendLine("SELECT ");
            sql.AppendLine("    COD_MOD, ");
            sql.AppendLine("    NOME_MOD, ");
            sql.AppendLine("    SIGL_MOD, ");
            sql.AppendLine("    DATA_ALTE, ");
            sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD) DATA_ALTE_MAX ");
            sql.AppendLine("FROM ");
            sql.AppendLine("    TFWDMOD ");
            sql.AppendLine("WHERE ");
            sql.AppendLine("    (COD_MOD = :COD_MOD OR :COD_MOD IS NULL) AND ");
            sql.AppendLine("    (UPPER(NOME_MOD) LIKE UPPER(:NOME_MOD) OR :NOME_MOD IS NULL) AND ");
            sql.AppendLine("    (UPPER(SIGL_MOD) LIKE UPPER(:SIGL_MOD) OR :SIGL_MOD IS NULL) ");
            sql.AppendLine("ORDER BY NOME_MOD ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.ModuleId);
                this.AddInParameter(command, "NOME_MOD", DbType.String, AddCharacterLike(entity.ModuleName));
                this.AddInParameter(command, "SIGL_MOD", DbType.String, AddCharacterLike(entity.ModuleInitials));

                response = this.FindAll<Entity.Module>(command,
                    (
                        reader => new Entity.Module
                        {
                            ModuleId = reader.GetValue<int>("COD_MOD"),
                            ModuleName = reader.GetValue<string>("NOME_MOD"),
                            ModuleInitials = reader.GetValue<string>("SIGL_MOD"),
                            //SubModules = GetSubModules(reader.GetValue<int>("COD_MOD"))
                            DateChange = reader.GetValue<DateTime>("DATA_ALTE"),
                            IsItemDivergent = !reader.GetValue<DateTime>("DATA_ALTE").Equals(reader.GetValue<DateTime>("DATA_ALTE_MAX"))
                        }
                    )
                );
            }

            return response;
        }

        public Entity.Module InsertModule(Entity.Module entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("INSERT INTO TFWDMOD ( ");
            sql.AppendLine("    COD_MOD, ");
            sql.AppendLine("    NOME_MOD, ");
            sql.AppendLine("    SIGL_MOD, ");
            sql.AppendLine("    DATA_ALTE ");
            sql.AppendLine("    ) VALUES ( ");
            sql.AppendLine("    :COD_MOD, ");
            sql.AppendLine("    :NOME_MOD, ");
            sql.AppendLine("    :SIGL_MOD, ");
            
            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine("    :DATA_ALTE) ");
            else
                sql.AppendLine("    (SELECT MAX(DATA_ALTE) FROM TFWDMOD)) ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.ModuleId);
                this.AddInParameter(command, "NOME_MOD", DbType.String, entity.ModuleName);
                this.AddInParameter(command, "SIGL_MOD", DbType.String, entity.ModuleInitials);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }

            return entity;
        }

        public void UpdateModule(Entity.Module entity)
        {
            StringBuilder sql = new StringBuilder();

            sql.AppendLine("UPDATE TFWDMOD SET ");
            sql.AppendLine("COD_MOD = :COD_MOD, ");
            sql.AppendLine("NOME_MOD = :NOME_MOD, ");
            sql.AppendLine("SIGL_MOD = :SIGL_MOD ");

            if (entity.DataOrigin == Entity.DataOrigin.Loader)
                sql.AppendLine(", DATA_ALTE = :DATA_ALTE ");

            sql.AppendLine("WHERE COD_MOD = :COD_MOD ");

            using (IDbCommand command = this.GetSqlStringCommand(sql.ToString()))
            {
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.ModuleId);
                this.AddInParameter(command, "NOME_MOD", DbType.String, entity.ModuleName);
                this.AddInParameter(command, "SIGL_MOD", DbType.String, entity.ModuleInitials);

                if (entity.DataOrigin == Entity.DataOrigin.Loader)
                    this.AddInParameter(command, "DATA_ALTE", DbType.Date, entity.DateChange);

                this.ExecuteNonQuery(command);
            }
        }

        public void DeleteModule(Entity.Module entity)
        {
            SubModule s = new SubModule();
            string sql = "DELETE FROM TFWDMOD WHERE COD_MOD = :COD_MOD ";

            s.DeleteSubModuleByModuleId(new Entity.SubModule { Module = entity });

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                this.AddInParameter(command, "COD_MOD", DbType.Int32, entity.ModuleId);
                this.ExecuteNonQuery(command);
            }
        }

        public int GetNextModuleId()
        {
            int retorno = 0;
            string sql = " SELECT NVL(MAX(COD_MOD) + 1, 1) FROM TFWDMOD ";

            using (IDbCommand command = this.GetSqlStringCommand(sql))
            {
                retorno = Convert.ToInt32(this.ExecuteScalar(command));
            }

            return retorno;
        }

        private List<Entity.SubModule> GetSubModules(int module_id)
        {
            SubModule t = new SubModule();
            List<Entity.SubModule> response = null;

            if (module_id != 0)
            {
                response = t.SelectSubModule(new Entity.SubModule { Module = new Entity.Module { ModuleId = module_id } });
            }

            return response;
        }

        private List<Entity.Attribute> GetAttributes(int id_type)
        {
            Attribute t = new Attribute();
            List<Entity.Attribute> response = null;

            if (id_type != 0)
            {
                response = t.SelectAttribute(new Entity.Attribute { ParentTypeId = id_type }, false);
            }

            return response;
        }

        #endregion
    }
}
